package com.zdkj.subway.check.controller.stationTiecketManager.xianWngCJXXQR.shouRuJS.reportError;

import com.zdkj.subway.check.util.DateFormat;
import com.zdkj.subway.check.util.JdbcUtil;
import org.apache.commons.lang3.StringUtils;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.*;

/**
 *  tvm工具，主要是金额获取,报表差错查询待提交入口类
 */
public class TvmReportErrorUtil {

    /**
     * tvm 收入结算待提交
     * @param param
     * @return
     * @throws SQLException
     */
    public static List<Map<String,Object>>  getXw_tvm_shouJieSuan_waitSubmit(Map<String,String> param) throws SQLException {
        String lineCode=param.get("lineCode");
        String stationCode=param.get("stationCode");
        String startSettleDate=param.get("startSettleDate");
        String endSettleDate=param.get("endSettleDate");
        String approvalStatus=param.get("approvalStatus");
        String confirmStatus = param.get("confirmStatus");
        String is_not_settle_status=param.get("is_not_settle_status");
        String is_report_settle_status=param.get("is_report_settle_status");
        String tvmCode=param.get("tvmCode");
        String preSubmitDate=param.get("preSubmitDate");

      
        String sql="select scd.id," +
                "scd.line_code lineCode," +
                "scd.station_code stationCode," +
                "scd.tvm_code tvmCode," +
                "scd.revenue_time revenueTime,\n" +
                "   ifnull(scd.check_amount,0)  - ifnull(vsc.SUPPLEMENT_AMOUNT,0) + ifnull(vsc2.CLEAR_OUT_AMOUNT,0) as cashReceiptAmount,\n" +
			    " ifnull(scd.check_amount,0) as checkAmount\n" +
               /*
                "        ,ifnull(sdb.daily_cash_total_amount,0) dailyCashTotalAmount\n" +
                "        ,ifnull(sdb.electronic_pay_total_amount,0) electronicPayTotalAmount\n" +
                "        ,ifnull(sdb.receive_total_money,0) billCashCashReceiptAmount\n" +
                "        ,ifnull(sdb.change_total_money,0) billCashCashChangeAmount,\n" +
                */
                //
                "        ,ifnull(0,0) dailyCashTotalAmount\n" +
                "        ,ifnull(0,0) electronicPayTotalAmount\n" +
                "        ,ifnull(0,0) billCashCashReceiptAmount\n" +
                "        ,ifnull(0,0) billCashCashChangeAmount,\n" +
                //
                
                "        sls.settle_period settlePeriod," +
                "sls.begin_settle_date log_beginSettleDate," +
                "sls.end_settle_date log_endSettleDate," +
                "sls.settle_date settleDate," +
                "#{startSettleDate} as startTime,\n" +
                "        ifnull(slps.SETTLEMENT_TERM,-1) settlementTerm," +
                "scd.check_person_one_jop_no operatorJobNo,\n" +
                "#{startSettleDate} js_startSettleDate,\n" +
                "#{endSettleDate} js_endSettleDate \n" +
                "        from sw_st_tvm_check_detail scd\n" +              
            
        
               /* 结账单去掉
                "        left join\n" +
                "        (select a.line_code,a.station_code,a.tvm_code,sum(a.daily_cash_total_amount) daily_cash_total_amount,sum(a.electronic_pay_total_amount) electronic_pay_total_amount,\n" +
                "        sum(b.RECEIVE_TOTAL_MONEY) RECEIVE_TOTAL_MONEY,sum(b.CHANGE_TOTAL_MONEY) CHANGE_TOTAL_MONEY,max(a.OPERATOR_JOB_NO) OPERATOR_JOB_NO\n" +
                "        from sw_st_tvm_daily_bill a\n" +
                "        left join sw_st_tvm_daily_bill_cash_pay b on a.id = b.RELATION_ID and a.del_flag='0' and b.del_flag='0'\n" +
                "        left join sw_st_tvm_daily_bill_electronic_pay c on a.id = c.RELATION_ID and c.del_flag='0' \n" +
                "        where a.DEL_FLAG ='0' and line_code=#{lineCode} and station_code=#{stationCode}\n" +
                "        and (a.START_TIME between #{startSettleDate} and #{endSettleDate}) and (a.END_TIME between #{startSettleDate} and #{endSettleDate})\n" +
                "        group by line_code,station_code,tvm_code) sdb on scd.line_code=sdb.line_code and scd.station_code=sdb.station_code and scd.tvm_code = sdb.tvm_code\n" +
                "\n" +*/
                "        left join\n" +
                "        (select LINE_CODE,STATION_CODE,TVM_CODE,sum(SUPPLEMENT_AMOUNT) SUPPLEMENT_AMOUNT\n" +
                "        from view_sw_st_tvm_supplement_currency\n" +
                "        where SUPPLEMENT_CURRENCY_TIME between #{startSettleDate} and #{endSettleDate}\n" +
                "        group by line_code,station_code,tvm_code) vsc on scd.line_code=vsc.line_code and scd.station_code=vsc.station_code and scd.tvm_code = vsc.tvm_code\n" +
                "        left join\n" +
                "        (select LINE_CODE,STATION_CODE,TVM_CODE,sum(CLEAR_OUT_AMOUNT+CLEAR_OUT_PAPER_AMOUNT) CLEAR_OUT_AMOUNT\n" +
                "        from view_sw_st_handwork_clean_record\n" +
                "        where CLEAN_TIME between #{startSettleDate} and #{endSettleDate}\n" +
                "        group by line_code,station_code,tvm_code) vsc2 on scd.line_code=vsc2.line_code and scd.station_code=vsc2.station_code and scd.tvm_code = vsc2.tvm_code\n" +
                "        left join sw_st_longperiod_settle_log sls on scd.line_code=sls.line_code and scd.station_code=sls.station_code and scd.tvm_code=sls.device_no\n" +
                "        left join\n" +
                "\n" +
                "        (select s.* from sw_pm_long_period_settlement s left join (select slps1.line_code,count(line_code) line_code_count\n" +
                "        from sw_pm_long_period_settlement slps1 where slps1.DEL_FLAG='0' and slps1.STATUS='1' GROUP BY line_code) slps2 on s.line_code=slps2.line_code and s.DEL_FLAG='0' and s.STATUS='1' where if(slps2.line_code_count > 1,\n" +
                "        s.STATION_CODE <>'' and s.STATION_CODE is not null ,1=1)) slps on if(slps.STATION_CODE <>'' and slps.STATION_CODE is not null,\n" +
                "        scd.LINE_CODE=slps.LINE_CODE and scd.STATION_CODE=slps.STATION_CODE,scd.LINE_CODE=slps.LINE_CODE)\n" +
                "\n" +
                "\n" +
                "        left join sw_st_tvm_income_settle ssis on ssis.DEL_FLAG = '0' and scd.line_code=ssis.line_code and scd.station_code=ssis.station_code and scd.tvm_code=ssis.tvm_code and ssis.RECORD_TIME=scd.REVENUE_TIME\n" +
                "       ";
        String tj=" where scd.DEL_FLAG = '0' ";
        if(StringUtils.isNotEmpty(approvalStatus)){
            tj+="  and scd.APPROVAL_STATUS = #{approvalStatus} ";
        }
        if(StringUtils.isNotEmpty(confirmStatus)){
            tj+="  and scd.CONFIRM_STATUS = #{confirmStatus} ";
        }
        if(StringUtils.isNotEmpty(is_not_settle_status)){
            tj+=" and (ssis.settle_status <> #{is_not_settle_status} or ssis.SETTLE_STATUS is null)";
        }
        if(StringUtils.isNotEmpty(is_report_settle_status)){
            tj+=" and ssis.settle_status = #{is_report_settle_status}";
        }
        if(StringUtils.isNotEmpty(lineCode)){
            tj+=" and scd.line_code=#{lineCode}";
        }
        if(StringUtils.isNotEmpty(stationCode)){
            tj+=" and scd.station_code=#{stationCode}";
        }

        if(StringUtils.isNotEmpty(startSettleDate) && StringUtils.isNotEmpty(endSettleDate)){
            tj+=" and date_add(scd.revenue_time,interval 12 hour) between #{startSettleDate} and #{endSettleDate}";
        }
        if(StringUtils.isNotEmpty(tvmCode)){
            tj+=" and scd.tvm_code=#{tvmCode}";
        }
        if(StringUtils.isNotEmpty(preSubmitDate)){
            tj+="  and DATE_FORMAT(sls.end_settle_date,'%y-%m-%d')=#{preSubmitDate}";
        }

        sql+=tj;
        sql=sql.replace("#{approvalStatus}","'"+approvalStatus+"'");
        sql=sql.replace("#{confirmStatus}","'"+confirmStatus+"'");
        sql=sql.replace("#{lineCode}","'"+lineCode+"'");
        sql=sql.replace("#{stationCode}","'"+stationCode+"'");
        sql=sql.replace("#{startSettleDate}","'"+startSettleDate+"'");
        sql=sql.replace("#{endSettleDate}","'"+endSettleDate+"'");
        sql=sql.replace("#{tvmCode}","'"+tvmCode+"'");
        sql=sql.replace("#{is_not_settle_status}","'"+is_not_settle_status+"'");
        sql=sql.replace("#{is_report_settle_status}","'"+is_report_settle_status+"'");
        sql=sql.replace("#{preSubmitDate}","'"+preSubmitDate+"'");
        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
        return maps;
    }

    /**
     * 票款差异审核主表-补币金额
     * @return
     */
    public static BigDecimal getSupplementAmount(Map<String,Object> param,String cashBoxType,String tvm_cash_box_num) throws SQLException, ParseException {
        Object lineCode=param.get("lineCode");
        Object stationCode=param.get("stationCode");
        Object tvmCode=param.get("tvmCode");
        Object startSettleDate = param.get("js_startSettleDate");
        Object endSettleDate = param.get("js_endSettleDate");
        if(startSettleDate==null){
            startSettleDate=param.get("settleStartTime");
        }
        if(endSettleDate==null){
            endSettleDate=param.get("settleEndTime");
        }
        String sql="SELECT ifnull(sum(sscd.SUPPLEMENT_AMOUNT),0) supplement_amount from sw_st_tvm_supplement_currency_details sscd left join sw_st_tvm_supplement_currency ssc\n" +
                "on sscd.RELATION_ID = ssc.id\n" +
                "where sscd.TVM_CODE='"+tvmCode+"' " +
                "and sscd.TVM_CASH_BOX_NUM = '"+tvm_cash_box_num+"'"+
                "and ssc.LINE_CODE='"+lineCode+"' " +
                "and ssc.STATION_CODE='"+stationCode+"' " +
                "and ssc.SUPPLEMENT_CURRENCY_TIME between '"+startSettleDate+"' and '"+endSettleDate+"' " +
                "and sscd.tvm_cash_box_type='"+cashBoxType+"'";

        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
        BigDecimal supplement_amount =(BigDecimal) maps.get(0).get("supplement_amount");
        return supplement_amount;
    }

    /**
     * tvm收入结算的预览功能
     * @return
     */
    public static List<Map<String, Object>> get_tvm_preview(Map<String,Object> param) throws SQLException, ParseException {

        List<Map<String, Object>> resList=new ArrayList<>();

        Object line_code=param.get("lineCode");
        Object tvmCode=param.get("tvmCode");
        Object revenueTime=param.get("revenueTime");
        if(revenueTime==null || revenueTime.equals("")){
            revenueTime=param.get("recordTime");
        }
        Object stationCode=param.get("stationCode");

        Object startSettleDate = param.get("js_startSettleDate");
        Object endSettleDate = param.get("js_endSettleDate");
        if(startSettleDate==null){
            startSettleDate=param.get("settleStartTime");
        }
        if(endSettleDate==null){
            endSettleDate=param.get("settleEndTime");
        }

        String unload_status_sa=" and scd.line_code='"+line_code+"' and station_code='"+stationCode+"'";
        List<Map<String, Object>> maps1 = JdbcUtil.exeSqlQuery("SELECT ifnull(sum(scrd.CLEAR_OUT_AMOUNT+scrd.CLEAR_OUT_PAPER_AMOUNT),0) clear_out_amount  from sw_st_handwork_clean_record scr left join sw_st_handwork_clean_record_details scrd on scr.id=scrd.RELATION_ID  \n" +
                "where scrd.del_flag='0' and scr.LINE_CODE='"+line_code+"' and scr.STATION_CODE='"+stationCode+"' " +
                "and  scr.CLEAN_TIME between '"+startSettleDate+"' and '" +endSettleDate+"'"+
                " and scrd.TVM_CODE='"+tvmCode+"'", "clear_out_amount");

        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery("SELECT *,scdc.id rid from sw_st_tvm_check_detail_coin scdc left join sw_st_tvm_check_detail scd\n" +
                "on scdc.RELATION_ID=scd.id and scdc.TVM_CODE='"+tvmCode+"' \n" +
                " and scd.del_flag=0 where scd.REVENUE_TIME='"+revenueTime+"' "+unload_status_sa ,"rid,real_amount,cashbox_code,unload_status,check_amount,ns_amount");

        if(maps.size()>0){ //硬币回收箱
            Map<String,Object> res=new HashMap<>();
            res.put("id",maps.get(0).get("rid"));//id
            res.put("type","硬币回收箱");//id
            res.put("cashBoxType",1);//硬币回收
            res.put("real_amount",maps.get(0).get("real_amount")); //清点
            res.put("cashbox_code",maps.get(0).get("cashbox_code")); //钱箱编号
            res.put("unload_status",maps.get(0).get("unload_status")); //卸载状态
            BigDecimal supplementAmount = getSupplementAmount(param,"1",String.valueOf(maps.get(0).get("cashbox_code")));
            res.put("ns_amount",supplementAmount); //补币
            res.put("clear_out_amount",maps1.get(0).get("clear_out_amount")); //清出
            resList.add(res);
        }else{
            Map<String,Object> res=new HashMap<>();
            res.put("type","硬币回收箱");//id
            res.put("cashBoxType",1);//硬币回收
//            BigDecimal supplementAmount = getSupplementAmount(param,"1","");
//            res.put("ns_amount",supplementAmount); //补币
            res.put("unload_status","0"); //卸载状态
            res.put("clear_out_amount",maps1.get(0).get("clear_out_amount")); //清出
            resList.add(res);
        }

        maps = JdbcUtil.exeSqlQuery("SELECT *,scdc.id rid from sw_st_tvm_check_detail_coin_change scdc left join sw_st_tvm_check_detail scd\n" +
                "on scdc.RELATION_ID=scd.id and scdc.TVM_CODE='"+tvmCode+"' \n" +
                "where scd.REVENUE_TIME='"+revenueTime+"'"+unload_status_sa,"rid,real_amount,cashbox_code,unload_status,check_amount,ns_amount");
        if(maps.size()>0){ //硬币找零
            Map<String,Object> res=new HashMap<>();
            res.put("id",maps.get(0).get("rid"));//id
            res.put("type","硬币找零箱");//id
            res.put("cashBoxType",2);//硬币找零
            res.put("real_amount",maps.get(0).get("real_amount")); //清点
            res.put("cashbox_code",maps.get(0).get("cashbox_code")); //钱箱编号
            res.put("unload_status",maps.get(0).get("unload_status")); //卸载状态
            BigDecimal supplementAmount = getSupplementAmount(param,"2",String.valueOf(maps.get(0).get("cashbox_code")));
            res.put("ns_amount",supplementAmount); //补币
            res.put("clear_out_amount",maps1.get(0).get("clear_out_amount")); //清出
            resList.add(res);
        }else{
            Map<String,Object> res=new HashMap<>();
            res.put("type","硬币找零箱");//id
            res.put("cashBoxType",2);//硬币找零
            res.put("unload_status","0"); //卸载状态
            resList.add(res);
        }

        maps = JdbcUtil.exeSqlQuery("SELECT *,scdc.id rid from sw_st_tvm_check_detail_paper scdc left join sw_st_tvm_check_detail scd\n" +
                "on scdc.RELATION_ID=scd.id and scdc.TVM_CODE='"+tvmCode+"' \n" +
                "where scd.REVENUE_TIME='"+revenueTime+"'"+unload_status_sa,"rid,real_amount,cashbox_code,unload_status,check_amount,ns_amount");
        if(maps.size()>0){ //纸币回收
            Map<String,Object> res=new HashMap<>();
            res.put("type","纸币回收箱");//id
            res.put("cashBoxType",3);//纸币回收
            res.put("real_amount",maps.get(0).get("real_amount")); //清点
            res.put("cashbox_code",maps.get(0).get("cashbox_code")); //钱箱编号
            res.put("unload_status",maps.get(0).get("unload_status")); //卸载状态
            BigDecimal supplementAmount = getSupplementAmount(param,"3",String.valueOf(maps.get(0).get("cashbox_code")));
            res.put("ns_amount",supplementAmount); //补币
            res.put("clear_out_amount",maps1.get(0).get("clear_out_amount")); //清出
            resList.add(res);
        }else{
            Map<String,Object> res=new HashMap<>();
            res.put("type","纸币回收箱");//id
            res.put("cashBoxType",3);//纸币回收
            res.put("unload_status","0"); //卸载状态
            resList.add(res);
        }

        maps = JdbcUtil.exeSqlQuery("SELECT *,scdc.id rid from sw_st_tvm_check_detail_paper_change scdc left join sw_st_tvm_check_detail scd\n" +
                "on scdc.RELATION_ID=scd.id and scdc.TVM_CODE='"+tvmCode+"' \n" +
                "where scd.REVENUE_TIME='"+revenueTime+"'"+unload_status_sa,"rid,real_amount,cashbox_code,unload_status,check_amount,ns_amount");
        if(maps.size()>0){ //纸币找零
            Map<String,Object> res=new HashMap<>();
            res.put("id",maps.get(0).get("rid"));//id
            res.put("type","纸币找零箱");//id
            res.put("cashBoxType",4);//纸币找零
            res.put("real_amount",maps.get(0).get("real_amount")); //清点
            res.put("cashbox_code",maps.get(0).get("cashbox_code")); //钱箱编号
            res.put("unload_status",maps.get(0).get("unload_status")); //卸载状态
            BigDecimal supplementAmount = getSupplementAmount(param,"4",String.valueOf(maps.get(0).get("cashbox_code")));
            res.put("ns_amount",supplementAmount); //补币
            res.put("clear_out_amount",maps1.get(0).get("clear_out_amount")); //清出
            resList.add(res);
        }else{
            Map<String,Object> res=new HashMap<>();
            res.put("type","纸币找零箱");//id
            res.put("cashBoxType",4);//纸币找零
            res.put("unload_status","0"); //卸载状态
            resList.add(res);
        }

        maps = JdbcUtil.exeSqlQuery("SELECT *,scdc.id rid from sw_st_tvm_check_detail_waste scdc left join sw_st_tvm_check_detail scd\n" +
                "on scdc.RELATION_ID=scd.id and scdc.TVM_CODE='"+tvmCode+"' \n" +
                "where scd.REVENUE_TIME='"+revenueTime+"'"+unload_status_sa,"rid,real_amount,cashbox_code,unload_status,check_amount,ns_amount");
        if(maps.size()>0){ //硬币回收箱
            Map<String,Object> res=new HashMap<>();
            res.put("id",maps.get(0).get("rid"));//id
            res.put("type","废钞箱");//id
            res.put("cashBoxType",5);//钱箱类型
            res.put("real_amount",maps.get(0).get("real_amount")); //清点
            res.put("cashbox_code",maps.get(0).get("cashbox_code")); //钱箱编号
            res.put("unload_status",maps.get(0).get("unload_status")); //卸载状态
            BigDecimal supplementAmount = getSupplementAmount(param,"5",String.valueOf(maps.get(0).get("cashbox_code")));
            res.put("ns_amount",supplementAmount); //补币
            res.put("clear_out_amount",maps1.get(0).get("clear_out_amount")); //清出
            resList.add(res);
        }else{
            Map<String,Object> res=new HashMap<>();
            res.put("type","废钞箱");//id
            res.put("cashBoxType",5);//钱箱类型
            res.put("unload_status","0"); //卸载状态
            resList.add(res);
        }
        return resList;
    }


    /**
     * 修改
     */
    public static boolean updateReportAlert(String name,String reportTime) throws SQLException {
        Integer integer = JdbcUtil.exeSqlCrud("update sw_st_report_error_alter_apply set IS_MODIFY=1 where REPROT_TIME='" + reportTime + "' and REPORT_NAME_ID\n" +
                "in(select ID from sw_pm_report_path sp where sp.REPORT_NAME ='" + name + "')");
        return true;
    }

    /**
     * 票款差异的sc lc acc现金
     * @param param
     * @param type
     * @return
     * @throws ParseException
     * @throws SQLException
     */
    public static Map<String,Object> getPiaoKuanCY_sc_lc_acc_cash(Map<String,Object> param,String type) throws ParseException, SQLException {
        Object revenueTime=param.get("revenueTime");

        Object startSettleDate = param.get("startSettleDate");
        Object endSettleDate = param.get("endSettleDate");
        String startDate=DateFormat.ex_strDate_to_date(startSettleDate+"","yyyyMMdd");
        String endDate=DateFormat.ex_strDate_to_date(endSettleDate+"","yyyyMMdd");

        String bookdate=DateFormat.ex_strDate_to_date(revenueTime+"","yyyyMMdd");
        List<Map<String, Object>> resp = JdbcUtil.exeSqlQuery("select ifnull(sum(sale_txnamtall),0) sale_txnamtall," +
                "ifnull(sum(recharge_txnamtall),0) recharge_txnamtall," +
                "ifnull(sum(refund_txnamtall),0) refund_txnamtall from "+type+"_fare_statistics " +
                "where equipmenttype='1'and station_equipment_number='" + String.valueOf(param.get("tvmCode")) + "'" +
                " and (paymethod='0' or paymethod='1' or paymethod='10'  OR paymethod is NULL or paymethod like '%3130%') and lineid='" + String.valueOf(param.get("lineCode")) +"'" +
                " and station_number='" + String.valueOf(param.get("stationCode")) +"'" +
//                " and bookdate='" +bookdate+"'"
                " and bookdate >='" +startDate+"' and bookdate <= '"+endDate+"'");

        return resp.get(0);
    }

    /**
     * 票款差异的sc lc acc电子
     * @param param
     * @param type
     * @return
     * @throws ParseException
     * @throws SQLException
     */
    public static Map<String,Object> getPiaoKuanCY_sc_lc_acc_ele(Map<String,Object> param,String type) throws ParseException, SQLException {
        Object revenueTime=param.get("revenueTime");

        Object startSettleDate = param.get("startSettleDate");
        Object endSettleDate = param.get("endSettleDate");
        String startDate=DateFormat.ex_strDate_to_date(startSettleDate+"","yyyyMMdd");
        String endDate=DateFormat.ex_strDate_to_date(endSettleDate+"","yyyyMMdd");

        String bookdate=DateFormat.ex_strDate_to_date(revenueTime+"","yyyyMMdd");
        List<Map<String, Object>> resp = JdbcUtil.exeSqlQuery("select ifnull(sum(sale_txnamtall),0) sale_txnamtall," +
                "ifnull(sum(recharge_txnamtall),0) recharge_txnamtall," +
                "ifnull(sum(refund_txnamtall),0) refund_txnamtall from "+type+"_fare_statistics " +
                "where equipmenttype='1'and station_equipment_number='" + String.valueOf(param.get("tvmCode")) + "'" +
                " and (paymethod='12' or paymethod='13' or paymethod='14' or paymethod='15') and lineid='" + String.valueOf(param.get("lineCode")) +"'" +
                " and station_number='" + String.valueOf(param.get("stationCode")) +"'" +
//                " and bookdate='" +bookdate+"'"
                " and bookdate >='" +startDate+"' and bookdate <= '"+endDate+"'");

        return resp.get(0);
    }

    /**
     * 票款差异分钱箱的sc
     * @param param
     * @return
     * @throws ParseException
     * @throws SQLException
     */
    public static Map<String,Object> getPiaoKuanCY_box_sc(Map<String,Object> param) throws ParseException, SQLException {

        String line_code=String.valueOf(param.get("line_code"));
        String station_code=String.valueOf(param.get("station_code"));
        String tvm_code=String.valueOf(param.get("tvm_code"));
        String cash_box_code=String.valueOf(param.get("cash_box_code"));
        String cash_box_type=String.valueOf(param.get("cash_box_type"));
        Date record_time = DateFormat.panDuanDate(param.get("record_time"));
        String bookdate = DateFormat.date_ex_str(record_time, "yyyyMMdd");

        String settleStartTime =DateFormat.ex_strDate_to_date(String.valueOf(param.get("income_settle_begin_time")),"yyyyMMdd");
        String settleEndTime = DateFormat.ex_strDate_to_date(String.valueOf(param.get("income_settle_end_time")),"yyyyMMdd");

        List<Map<String, Object>> resp = JdbcUtil.exeSqlQuery("select " +
                "ifnull(sum(amount),0) amount," +
                "ifnull(sum(supplement_amount),0) supplement_amount " +
                "from sc_tvm_coin_opt where station_equipment_number = '"+tvm_code+"'"+
                " and coin_number='"+cash_box_code+"'"+
                " and cash_box_type='"+cash_box_type+"'"+
                " and lineid='"+line_code+"' and station_number='"+station_code+"'"+
//                " and bookdate='" + bookdate+"'"
		        " and opt_type='2'"+  //2 卸载 1补币
                " and bookdate >='" + settleStartTime+"' and bookdate<='"+settleEndTime+"'");

        return resp.get(0);
    }
}
